package com.fqy.dao;


import com.fqy.dao.impl.PeopleDao;
import com.fqy.entity.People;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static com.fqy.servlet.IndexServlet.IS;

public class PeopleDaoImpl implements PeopleDao {
    public int insertpeople(People people) {

        int n = 0;
        String sql = "insert into people values (?,?,?)";
        Connection conn = IS.con;
        PreparedStatement ps = null;
        try {
            // 创建会话
            ps = conn.prepareStatement(sql);
            // 设置 ? 的值
            ps.setInt(1, people.getId());
            ps.setString(2, people.getName());
            ps.setString(3, people.getSexuality());

            // 执行sql
            n = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {     // 关闭数据库资源
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return n;
    }

    public int deletePeople(int id) {
        String sql = "delete from people where id=?";
        int n = 0;
        Connection conn = IS.con;
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);
            // 设置 ? 的值
            ps.setInt(1, id);
            // 执行sql
            n =  ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return n;
    }

    public int updatePeople(People people) {
        String sql = "update people set name=?, sexuality=? where id=?";
        int n = 0;
        Connection conn =  IS.con;
        PreparedStatement ps = null;
        try {
            // 创建会话
            ps = conn.prepareStatement(sql);
            ps.setString(1, people.getName());
            ps.setString(2, people.getSexuality());
            ps.setInt(3, people.getId());
            n = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return n;
    }

    public People selectPeopleByName(String name) {
        String sql = "select * from people where bookName=?";
        People jdbc = null;
        Connection conn = IS.con;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 创建会话
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();

            if (rs.next()) {
                int id = rs.getInt("id");
                String name1 = rs.getString("name");
                String sexuality = rs.getString("sexuality");
                jdbc = new People(id, name1, sexuality);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jdbc;
    }

    public List<String> selectAllPeople() {
        String sql = "select * from people";
        List<String> list = new ArrayList<>();
        Connection conn = IS.con;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            // 获取结果集
            rs = ps.executeQuery();
            // 遍历结果集

            while (rs.next()) {
                int id = rs.getInt("id");
               String name = rs.getString("name");
                String sexuality = rs.getString("sexuality");
               String s1="结果是"+" "+id+" "+name+" "+sexuality;

                list.add(s1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
    }

